/**
 * Company : Thai Informatic System Co., Ltd.
 * Created By : Chatree Siangklom
 * Created On : Apr 10, 2013  11:24:41 AM 
 * e-Mail : dr_chatree@hotmail.com
 * Mobile Phone : (+66) 087-921-5115
 * Package Name : promis.api.excel.xlsx
 * File Name : ExampleExcelXlsx.java
 * TODO
 */
package promis.api.excel.xlsx;

import java.io.FileInputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * @author chatree
 *
 */
public class ExampleExcelXlsx {

	private void ReadExcelXLS() throws Exception {
		String filename = "C:\\Users\\chatree\\workspace\\ExcelTested\\excel_store\\PODuplicated.xlsx"; 
		if (filename != null && !filename.equals("")) {	
	            try
	            {
	                //Create a poi workbook from the excel spreadsheet file
	                POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename));
	                HSSFWorkbook wb = new HSSFWorkbook(fs);

	                for (int k = 0; k < wb.getNumberOfSheets(); k++)
	                {			
	                    HSSFSheet sheet = wb.getSheetAt(k);	                	
	                    int  rows  = sheet.getPhysicalNumberOfRows();
	                    
	                    for (int r = 0; r < rows; r++)
	                    {
	                        HSSFRow row   = sheet.getRow(r);
	                        if (row != null) { 
	                            int cells = row.getPhysicalNumberOfCells();
	                            for (short c = 0; c < cells; c++) 
	                            { 
	                                HSSFCell cell  = row.getCell(c);	                            	
	                                if (cell != null) { 
	                                    String   value = null;
	                                    switch (cell.getCellType())
	                                    {
	                                        case HSSFCell.CELL_TYPE_FORMULA :
	                                        	value = "FORMULA ";
	                                            break;
	                                        case HSSFCell.CELL_TYPE_NUMERIC :
	                                            value = "NUMERIC value=" + cell.getNumericCellValue();
	                                            break;
	                                        case HSSFCell.CELL_TYPE_STRING :
	                                            value = "STRING value=" + cell.getStringCellValue();
	                                            break;
	                                        case HSSFCell.CELL_TYPE_BOOLEAN :
	                                        	value = "BOOLEAN value=" + cell.getBooleanCellValue();
	                                        	break;
	                                        case HSSFCell.CELL_TYPE_ERROR :
	                                        	value = "ERROR value=" + cell.getErrorCellValue();
	                                        	break;	                                        	
	                                        default :
	                                    }
	                                    
	                                    System.out.println(c + ".Value:" + value);
	                                } 
	                            }
	                        }
	                    }
	                }
	            }
	            catch (Exception e)
	            {
	                e.printStackTrace();
	            }
		} 
	}
	
	private void ReadExcelXLSX() throws Exception {
		String filename = "C:\\Users\\chatree\\workspace\\ExcelTested\\excel_store\\PODuplicated.xlsx"; 
		if (filename != null && !filename.equals("")) {	
	            try
	            {
	                //Create a poi workbook from the excel spreadsheet file
	                XSSFWorkbook wb = new XSSFWorkbook(filename);

	                for (int k = 0; k < wb.getNumberOfSheets(); k++)
	                {			
	                	XSSFSheet sheet = wb.getSheetAt(k);
	                    int  rows  = sheet.getPhysicalNumberOfRows();
	                    
	                    for (int r = 0; r < rows; r++)
	                    {
	                        XSSFRow row   = sheet.getRow(r);
	                        if (row != null) { 
	                            int cells = row.getPhysicalNumberOfCells();
	                            for (short c = 0; c < cells; c++) 
	                            { 
	                            	XSSFCell cell  = row.getCell(c);
	                                if (cell != null) { 
	                                    String   value = null;
	                                    switch (cell.getCellType())
	                                    {
	                                        case XSSFCell.CELL_TYPE_FORMULA :
	                                        	value = "FORMULA ";
	                                            break;
	                                        case XSSFCell.CELL_TYPE_NUMERIC :
	                                            value = "NUMERIC value=" + cell.getNumericCellValue();
	                                            break;
	                                        case XSSFCell.CELL_TYPE_STRING :
	                                            value = "STRING value=" + cell.getStringCellValue();
	                                            break;
	                                        case XSSFCell.CELL_TYPE_BOOLEAN :
	                                        	value = "BOOLEAN value=" + cell.getBooleanCellValue();
	                                        	break;
	                                        case XSSFCell.CELL_TYPE_ERROR :
	                                        	value = "ERROR value=" + cell.getErrorCellValue();
	                                        	break;	                                        	
	                                        default :
	                                        	value = "default ERROR";
	                                    }
	                                    
	                                    System.out.println(c + ".Value:" + value);
	                                } 
	                            }
	                        }
	                    }
	                }
	            }
	            catch (Exception e)
	            {
	                e.printStackTrace();
	            }
		} 
	}


	/**
	 * @param args
	 */
	public static void main(String[] args) {
		try{
			new ExampleExcelXlsx().ReadExcelXLSX();
			
		}catch(Exception e){
			e.printStackTrace();
		}

	}

}
